SQLite
thumb Про SQLite Вступ SQLite - це відкрита вбудована реляційна система керування базами даних. Вона була створена для надання зручного способу керування даними у застосунках без накладних витрат, які часто виникають при використані СКБД. Основними характеристиками SQLite є високий рівень мобільності (незалежності від платформи), простота використання, ефективність, надійність. Перший випуск датується 2000-м роком. Наразі актуальною є версія 3.7.6.2. Код SQLite написаний на мові програмування C, має досить невеликий розмір, близько 300 кілобайт, це дозволяє використовувати цю СКБД на малопотужних пристроях (наприклад iPhone). Історія Автором SQLite є Д. Річард Хіп (D. Richard Hipp). Він працював у General Dynamics для програми флоту США, розробляв програмне забезпечення для використання на есмінцях. Програма працювала на Hewlett-Packard Unix (HPUX) та використовувала базу даних Informix. Проте, для деяких застосунків Informix був дуже громіздким і потребував багато ресурсів. Також підтримка системи в актуальному стані потребувала складної роботи адміністраторів баз даних, водночас програмісти потребували легкої БД, яка була доступна всюди, та могла легко переноситись разом з програмою. В січні 2000, Хіп з колегами обговорили ідею створення простої, вбудованої SQL бази даних, яка буде використовувати GNU DBM B-Tree library (gdbm), як бек-енд, що дозволить використовувати її без необхідності встановлення та адміністративної підтримки. Перша версія була готова у серпні 2000-го, це була SQLite 1.0. Як і планувалось, SQLite 1.0 використовувала gdbm, як систему зберігання (storage manager). Але згодом, Хіп замінив gdbm власною реалізацією B-дерев, яка підтримувала транзакції та зберігала записи за впорядкуванням ключів. З того часу SQLite почала свою поступову еволюцію, зростаючи, як за кількістю нововведень так і за кількістю користувачів. В середині 2001-го багато відкритих та комерційних проектів починають використовувати SQLite. Це призвело до того, що члени відкритої спільноти розробників починають писати різні розширення та бібліотеки для даної СКБД. Основні нововведення в SQLite 3: використання UTF-8 та UTF-16, покращений C API, явне оголошення типів, зменшення розміру баз даних, BLOB, 64-bit ROWIDs, autovacuum, покращену систему паралельного доступу. Використання Встановлення Встановлення Існує багато реалізацій SQLite для різних платформ. Для прикладу буде проводиться встановлення для двох дистрибутивів Linux, а саме Ubuntu 10.10 та CenntOS 5.5. Встановлення в Ubuntu: $ sudo apt-get install sqlite3 Встановлення в CentOS: $ sudo yum install sqlite Окрім зв'язки з певною мовою програмування SQLite має власний інтерфейс для командної стрічки. Він називається sqlite3 і його ми і будемо використовувати для демонстрації роботи з SQLite. Робота з інтерфейсом sqlite3 Запуск відбувається наступним чином: $ sqlite3 Після цього запрошення змінить вигляд на: sqlite> Для отримання допомоги та виходу відповідно, виконуємо наступні команди: sqlite> .help sqlite> .exit Взагалі існує декілька способів вийти, ось деякі з них: .q, .quit та вже згадана команда .exit. Для демонстрації можливостей sqlite3 використаємо файл test.sql (http://pastebin.calculate-linux.ru/ru/show/376) Увійдемо в sqlite3: $ sqlite3 test.db sqlite> Зчитаємо файл test.sql: sqlite> .read test.sql Переглянемо таблиці, які створилися: sqlite> .tables Cars Переглянути структуру таблиць: sqlite> .schema Cars CREATE TABLE Cars(Id integer PRIMARY KEY, Name text, Cost integer); Виконаємо простий запит: sqlite> select * from Cars; 1|Audi|52642 2|Mercedes|57127 3|Skoda|9000 4|Volvo|29000 5|Bentley|350000 6|Citroen|21000 7|Hummer|41400 8|Volkswagen|21600 Роздільником є символ "|" його можна змінити наприклад на ":" за допомогою команди: sqlite> .separator : Існує декілька режимів виводу. Розглянемо декілька з них. Перший цікавий режим - це стовпчиковий вивід: sqlite> .mode column sqlite> .headers on sqlite> select * from Cars; Id Name Cost ---------- ---------- ---------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 Виглядає набагато краще, але ще можна змінити ширину стовпчиків, наприклад так: sqlite> .width 4, 15, 7 sqlite> select * from Cars; Id Name Cost ---- --------------- ------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 Ще один цікавий режим виводу це вивід в HTML-таблицях sqlite> .mode html sqlite> select * from Cars; Id Name Cost 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 Команда .show показує список налаштувань та їх значень: sqlite> .show echo: off explain: off headers: on mode: column nullvalue: "" output: stdout separator: "|" stats: off width: 4 15 7 Ці налаштування діють до кінця роботи з sqlite3, щоб кожного разу задавати їх знову налаштування можна зберігати у файлі ~/.sqliterc у форматі: .mode column .header on .nullvalue NULL Тоді при запуску sqlite3 виводиться наступне повідомлення: $ sqlite3 -- Loading resources from /home/vamel/.sqliterc Замість запуску інтерфейсу командної стрічки самої SQLite, можна виконати потрібні дії не полишаючи командної оболонки ОС, це досить зручний для використання SQLite у shell-скриптах: $ sqlite3 test.db "SELECT * FROM Cars;" Створення дампу таблиці: sqlite> .dump Cars PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE Cars(Id integer PRIMARY KEY, Name text, Cost integer); INSERT INTO "Cars" VALUES(1,'Audi',52642); INSERT INTO "Cars" VALUES(2,'Mercedes',57127); INSERT INTO "Cars" VALUES(3,'Skoda',9000); INSERT INTO "Cars" VALUES(4,'Volvo',29000); INSERT INTO "Cars" VALUES(5,'Bentley',350000); INSERT INTO "Cars" VALUES(6,'Citroen',21000); INSERT INTO "Cars" VALUES(7,'Hummer',41400); INSERT INTO "Cars" VALUES(8,'Volkswagen',21600); COMMIT; Вивід дампу відбувається на екран, краще записати його зразу у файл: sqlite> .output cars.sql sqlite> .dump Cars sqlite> .output stdout Команда ".output stdout" повертає вивід на екран, якщо її не виконати то весь вивід було здійснюватися у файл. Відновлення з дампу відбувається наступним чином: sqlite> .read cars.sql Засоби адміністрування SQLite Програми для адміністрування SQLite. Програма sqlite3 дає простий доступ до даних та створення резервних копій, тощо. Але постійно її використовувати не завжди зручно, тому для полегшення роботи з SQLite створено ряд програмних засобів. На сторінці розробника SQLite є дуже довгий список можливих засобів. Всі вони є досить гарними продуктами, тому доводиться ввести декілька критеріїв для оцінки. * Перш за все треба виділити засоби за доступністю, тобто програми які є безкоштовними та ліцензія на які не створює додаткових проблем. * Другий критерій - це доступність на різних операційних системах. Це досить важлива причина оскільки користувачі використовують різні ОС, але хочуть користуватися тими самими інструментами незалежно від вибору операційної системи. *Третій критерій - це легкість встановлення, тут слід зазначити наявність простої процедури встановлення, наприклад з пакунку чи бінарного файлу. * Четвертий критерій - це те як часто виходять оновлення, дата останнього релізу, тощо. Оскільки існує багато вже "мертвих" програмних продуктів, які вже не підтримуються розробниками та використовують старі технології. * Також зважаючи на характеристики та призначення SQLite будуть розглянуті лише засоби, що працюють локально та не потребують встановлення великої кількості додаткових сервісів (наприклад веб-сервера, додаткових інтерпретаторів тощо). Sqliteman Файл:Sqliteman.png Ліцензія: GPLv2 Веб-сайт: sqliteman.com Операційні системи: Windows, Linux, Mac OS X Графічний інтерфейс створений за допомогою Qt. Основні характеристики: * повна підтримка команди ALTER TABLE * можливість переглядати BLOB * заповнення таблиці тестовими даними (добре для тестування) * імпорт, експорт * SQL-редактор * перегляд схеми Встановлення: sudo apt-get install sqliteman SQLiteStudio Файл:Sqlitestudio.png Ліцензія: GPLv2 Веб-сайт: sqlitestudio.one.pl Операційні системи: Windows, Linux, Mac OS X Основні характеристики: * MDI-інтерфейс * Широкий вибір локалізації * експорт у багатьох форматах(SQL, CSV, HTML, XML) * підтримка як SQLite2 так і SQLite3 * підтримка UTF-8 Встановлення Непотрібно встановлювати, лише один виконувальний файл. OpenOffice.org Base Файл:Openoffice.png Ліцензія: LGPL Веб-сайт: www.openoffice.org Основні характеристики: * дуже схожий на MS Access Встановлення Основний пакунок OpenOffice.org містить OpenOffice.org Base, додатково встановлюємо ще декілька пакунків: sudo apt-get install unixodbc unixodbc-bin sudo apt-get install libsqliteodbc sudo ODBCConfig SQLiteDatabaseBrowser Файл:Sqlitebrowser.png Ліцензія: Public Domain Веб-сайт: sqlitebrowser.sourceforge.net Використовує Qt для GUI Основні характеристики: * визначення та ствопрення таблиць * зміна, модифікація, видалення таблиць * створення індексів * імпорт, експорт * перегляд записів * логування SQL-каманд Встановлення sudo apt-get install sqlitebrowser Доступ з різних мов програмування Використання SQLite з скриптів. Як вже зазначалося SQLite можна дуже легко використовувати з shell-скриптів за допомогою sqlite3. Для прикладу наведемо приклад програми з використанням мови програмування Python 3: #!/usr/bin/python3 import sqlite3 def main(): db = sqlite3.connect('test.db') db.execute('drop table if exists test') db.execute('create table test (t1 text, i1 int)') db.execute('insert into test (t1, i1) values (?, ?)', ('one', 1)) db.execute('insert into test (t1, i1) values (?, ?)', ('two', 2)) db.commit() cursor = db.execute('select * from test order by i1') for row in cursor: print(row) if __name__ "__main__": main() Використання SQLite в мережі До баз SQLite можна отримати доступ по мережі. Один з можливих способів - це використати мережеву файлову систему. Інший спосіб - це засоби спеціально створені для такого доступу. NFS Для віддаленого доступу до файлів у Unix-подібних системах використовують мережеву файлову систему NFS. Для прикладу є кластер який складається з головного вузла та трьох обчислювальних вузлів. На головному вузлі працює NFS-сервер, а на обчислювальних вузлах монтуються домашні каталоги користувачів. Далі наведений порядок роботи: Переглянемо примонтовані файлові системи: ~$ mount ... /dev/mapper/homevg-home on /home type ext3 (rw) ... ~$ df -h ... /dev/mapper/homevg-home 897G 433G 419G 51% /home Створимо каталог для баз даних: ~$ mkdir sqlite ~$ cd sqlite Створимо базу даних та наповнимо її тестовими даними: sqlite$ sqlite3 nfs.db sqlite> create table t1 (i1 integer, t1 text); sqlite> .tables t1 sqlite> insert into t1 (i1, t1) values (1, 'hello'); sqlite> insert into t1 (i1, t1) values (2, 'world'); sqlite> select * from t1; 1|hello 2|world sqlite> .q Перейдемо на перший обчислювальний вузол: sqlite$ ssh node1 Проглянемо примонтовані ФС: ~$ mount ... 192.168.17.1:/home on /home type nfs (rw,addr=192.168.17.1) ... Перейдемо в каталог з базою даних: ~$ ls comm list mpi sqlite test tmp ~$ cd sqlite/ Запустимо sqlite3 та виконаємо декілька операцій: sqlite$ sqlite3 nfs.db sqlite> select * from t1; 1|hello 2|world sqlite> insert into t1 (i1, t1) values (3, 'remote hello'); sqlite> insert into t1 (i1, t1) values (4, 'remote world'); sqlite> select * from t1; 1|hello 2|world 3|remote hello 4|remote world sqlite> .q Перейдемо до другого вузла: sqlite$ ssh node2 ~$ mount ... 192.168.17.1:/home on /home type nfs (rw,addr=192.168.17.1) ... ~$ df -h ... 192.168.17.1:/home 897G 433G 419G 51% /home ... Попрацюємо з sqlite3 ~$ sqlite3 sqlite/nfs.db sqlite> .tables t1 sqlite> select * from t1; 1|hello 2|world 3|remote hello 4|remote world sqlite> .q Повернемося на головний вузол та перевіримо чи відбулися зміни: ~$ exit sqlite$ exit sqlite$ sqlite3 nfs.db "select * from t1;" 1|hello 2|world 3|remote hello 4|remote world Як видно все працює. Проте NFS версії 3 та 2 відома своїми проблемами з безпекою. Тому застосування NFS краще обмежити локальними мережами. SSHFS Протокол SSH відомий своїм ставленням до безпеки. Його широко використовують адміністратори, тому SSH-сервер працює майже на кожному сервері. Тому дуже перспективною можливістю стає використання SSHFS. Для роботи не потрібно запускати додаткових сервісів, треба лише встановити декілька додаткових пакунків: sudo apt-get install sshfs Монтування проходить наступним чином: $ sshfs vamel@194.44.34.19:/home/vamel/sqlite db Перевіримо чи з'явилися база: $ ls nfs.db Створимо нову базу даних та попрацюємо з нею: $ sqlite3 sshfs.db sqlite> .tables sqlite> create table ssh (id integer, name string); sqlite> insert into ssh values(1, "Pretro"); sqlite> insert into ssh values(2, "Ivan"); sqlite> insert into ssh values(3, "Mykola"); sqlite> select * from ssh; id name ---------- ---------- 1 Pretro 2 Ivan 3 Mykola sqlite> .exit Відмотування проходить так: $ fusermount -u db Перевіримо чи збереглася база на віддаленому вузлі: $ ssh vamel@194.44.34.19 $ cd sqlite $ sqlite3 sshfs.db sqlite> .tables ssh sqlite> select * from ssh; 1|Pretro 2|Ivan 3|Mykola Так база збереглася на віддаленому вузлі. Висновки Використання цієї СКБД дозволяє зробити сайт мобільним, швидким та легким у підтримці. Як завжди вона має свої недоліки , але зважаючи на сферу застосування SQLite має перспективні шанси зайняти упевнені позиції в розробці для Web. Посилання Інтернет посилання офіційний сайт офіційна документація список програм для адміністрування SQLite стаття в ru.wokipedia.org SQLite tutorial SQLite tutorial SQLite tutorial SQLite tutorial мережеве використання SQLite Обзор SQLite Посилання на вихідні коди та дампи test.sql